USE [ARR]
GO
/****** Object:  StoredProcedure [dbo].[Report_StoreCoverage_Penetration]    Script Date: 11/23/2011 16:28:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Report_StoreCoverage_Penetration 1, 7
CREATE PROCEDURE [dbo].[Report_StoreCoverage_Penetration]
	   @RangeReviewId int
	 , @CategoryId smallint
AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStoresCoverage INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and SKUCoverage = 1)

Select 
	s.StoreId
	, s.ArticleId
	, rrs.SKUCoverage 
	, rrs.SKUPenetration
	, SUM(s.Sales) Sales
	, rr.StoreSegmentation1
	, rr.StoreSegmentation2
	, store.Region
	, store.Subregion
	, store.City
	, store.State
	, store.ShoppingOccasion
	, store.Size
	, store.Format
	, store.ShopType
	, store.Layout
	, store.OpeningHours
	, store.SELCustomers
	, store.SELLocation
	, store.SEL
	, store.Attribute1
	, store.Attribute2
	, @totalStoresCoverage TotalStoresCoverage
From ArticleSales s
Inner Join Articles a on s.ArticleId = a.Id and a.RangeReviewId = @RangeReviewId
Inner Join Categories c on a.CategoryId = c.Id and (a.CategoryId = @CategoryId OR c.ParentId = @CategoryId)
Inner Join RangeReviewStores rrs on s.StoreId = rrs.StoreId and (rrs.SKUCoverage = 1 OR rrs.SKUPenetration = 1)
Inner Join RangeReviews rr on rrs.RangeReviewId = rr.Id
Inner Join Stores store on rrs.StoreId = store.Id
Where
	s.Date between @startDate and @endDate	
Group By
	s.StoreId
	, s.ArticleId
	, rrs.SKUCoverage 
	, rrs.SKUPenetration
	, rr.StoreSegmentation1
	, rr.StoreSegmentation2
	, store.Region
	, store.SubRegion
	, store.City
	, store.State
	, store.ShoppingOccasion
	, store.Size
	, store.Format
	, store.ShopType
	, store.Layout
	, store.OpeningHours
	, store.SELCustomers
	, store.SELLocation
	, store.SEL
	, store.Attribute1
	, store.Attribute2
	
	